package com.apes.fn.business.carAccident.repository;

import com.apes.fn.business.carAccident.modle.CarAccident;
import com.apes.fn.business.fixedLoss.modle.LossQuotation;
import com.apes.fn.business.fixedLoss.modle.LossQuotationItem;
import com.apes.fn.server.productRequire.ProductRequire;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.frame.model.Dept;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
 * @ClassName : CarAccidentRepository
 * @Description : 车险报案单
 * @Author : WSJ
 * @Date: 2020-03-07 14:41
 */
public interface CarAccidentRepository extends CustomRepository<CarAccident,String> {
    /**
     * 查询该报案单是否有未完成的相关要货单
     *
     * @return
     */
    @Query("select b from ProductRequire b  where b.state in ('create','approve') " +
            " and b.source in (select a.id from LossQuotation a where a.carAccident = :carAccidentId)")
    List<ProductRequire> findProductRequireForID(@Param("carAccidentId") String ID);


    /**
     * 查询该报案单是否有已转服务的定损报价明细
     *
     * @return
     */
    @Query("select b from LossQuotationItem b,LossQuotation a " +
            " where a.id = b.lossQuotation and b.servicePlan is not null and a.carAccident = :carAccidentId ")
    List<LossQuotationItem> findLossQuotationItemForID(@Param("carAccidentId") String ID);

    /**
     * 查询该报案单对应的定损报价单
     *
     * @return
     */
    @Query("select a from LossQuotation a where a.carAccident = :carAccidentId ")
    List<LossQuotation> findLossQuotationForID(@Param("carAccidentId") String ID);

    /**
     * 查询未完成定损的车险报案单
     * @param carNumber 车牌
     * @param dept 门店编码
     * @return
     */
    @Query("select a from CarAccident a  where a.carNumber = :carNumber " +
            "  and a.dept.id = :dept and a.state not in ('repeal','fixedLoss')")
    List<CarAccident> findCarAccidentByCarNumber(@Param("carNumber") String carNumber, @Param("dept") String dept);

    /**
     * 查询未走完定损的车险报案单
     *
     * @return
     */
    @Query("select a from CarAccident a ,LossQuotation b where a.carNumber = :carNumber " +
            "  and a.dept = :dept and a.id = b.carAccident and b.state not in ('businessAudit','repeal')")
    List<CarAccident> findffectiveCarAccidentByCarNumber(@Param("carNumber") String carNumber, @Param("dept") Dept dept);


    /**
     * 查找门店定损额度
     * @param dept 门店编码
     * @return
     */
    @Query(value = " SELECT A.DEPT_ID,A.SURPLUS_QUOTA FROM FIXED_LOSS_LIMIT_PER_MONTH A, " +
            " (SELECT MAX(B.STATISTIC_DATE)STATISTIC_DATE,B.DEPT_ID FROM FIXED_LOSS_LIMIT_PER_MONTH B GROUP BY B.DEPT_ID ) C " +
            " WHERE A.DEPT_ID = C.DEPT_ID AND A.STATISTIC_DATE = C.STATISTIC_DATE AND A.DEPT_ID =:dept",nativeQuery = true)
    List<Map> findStoreQuota(@Param("dept") String dept);

}
